package tsli.acm.ctrl;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

import tsli.acm.biz.AgentDebtManagement;
import tsli.acm.dao.GAAgentStructureDAO;
import tsli.acm.dao.GACommissionPositiveDAO;
import tsli.acm.dao.SFCAgentStructureDAO;
import tsli.acm.dao.SFCCommissionNegativeDAO;
import tsli.acm.dao.SFCCommissionPositiveDAO;
import tsli.acm.database.Database;

public class ACM1104Srvl extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	public void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		if (request.getParameter("process_type").equals("getDataTable")) {
			getDataTable(request, response);
		} else if (request.getParameter("process_type").equals("quarter")) {
			getQuarter(request, response);
		} else if (request.getParameter("process_type").equals("annual")) {
			getAnnual(request, response);
		} else if (request.getParameter("process_type").equals("showData")) {
			showData(request, response);
		}else if (request.getParameter("process_type").equals(
				"getDataDetailTable")) {
			getDataDetailTable(request, response);
		}
	}

	
	@SuppressWarnings("unchecked")
	public void getDataTable(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		String paAgentNo = request.getParameter("sSearch_0").trim();
		String paCalculationType = request.getParameter("sSearch_1").trim();
		String paQuarterly = request.getParameter("sSearch_2").trim();
		String paYear = request.getParameter("sSearch_3").trim();
		String paMonth1 = "";
		String paMonth2 = "";
		String paMonth3 = "";
		if (paAgentNo.equals("")) {
			paAgentNo = "%";
		}
		if (paQuarterly.equals("1")) {
			paMonth1 = paYear + "01";
			paMonth2 = paYear + "02";
			paMonth3 = paYear + "03";
		} else if (paQuarterly.equals("2")) {
			paMonth1 = paYear + "04";
			paMonth2 = paYear + "05";
			paMonth3 = paYear + "06";
		} else if (paQuarterly.equals("3")) {
			paMonth1 = paYear + "07";
			paMonth2 = paYear + "08";
			paMonth3 = paYear + "09";
		} else if (paQuarterly.equals("4")) {
			paMonth1 = paYear + "10";
			paMonth2 = paYear + "10";
			paMonth3 = paYear + "11";
		}
		String paPersistencyCategory = "S11030B10107";
		Database db = new Database();
		GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(
				db);
		
		GAAgentStructureDAO voGAAgentStructure = new GAAgentStructureDAO(db);
    	List<Map<String, Object>> vaVPInfo = voGAAgentStructure.findVPData(paAgentNo);
		

    	double vnPc = 0;
		double vnBonusRate = 0;
		double vnBonus = 0;
		double vnPersistencyCredit = 0;
		double vnPersistencyRate = 0;
		int vnExperience = 0;
		double vnQuarterlySumBonus = 0;
		double vnAnnualBonus = 0;
		String vaAgentNo = null;
		String vaAgentName = null;
		DecimalFormat df = new DecimalFormat("#,##0.00");
		PrintWriter voOut = response.getWriter();
		JSONObject voObject = new JSONObject();
		JSONArray voListData = new JSONArray();

		int size = 0;
		if (vaVPInfo != null) {
			size = vaVPInfo.size();
		}
		if(!paYear.equals("")){
			for (int i = 0; i < size; i++) {
				vaAgentNo = vaVPInfo.get(i).get("PE_NO").toString();

				if (paCalculationType.equals("quarter")) {
					vnPc = Double.parseDouble(voGACommissionPositiveDAO.getVPPCQuarter(
							vaAgentNo, paMonth1, paMonth2, paMonth3));

				} else {
					vnPc = Double.parseDouble(voGACommissionPositiveDAO.getVPPCAnnual(
							vaAgentNo, paYear));

				}
				
				
				vnBonusRate = 0;
				vnBonus = 0;
				vnPersistencyCredit = 0;
				vnPersistencyRate = 0;
				vnExperience = 0;

				vaAgentName = voGACommissionPositiveDAO.findNameById(vaAgentNo);

				if (paCalculationType.equals("quarter")) {
					if (Math.abs(vnPc) >= 0 && Math.abs(vnPc) <= 2399999) {
						vnBonusRate = 0;
					} else if (Math.abs(vnPc) >= 2400000 && Math.abs(vnPc) <= 4799999) {
						vnBonusRate = 0.005;
					} else if (Math.abs(vnPc) >= 4800000 && Math.abs(vnPc) <= 7199999) {
						vnBonusRate = 0.01;
					} else if (Math.abs(vnPc) >= 7200000 && Math.abs(vnPc) <= 9599999) {
						vnBonusRate = 0.015;
					} else if (Math.abs(vnPc) >= 9600000 && Math.abs(vnPc) <= 11999999) {
						vnBonusRate = 0.02;
					} else {
						vnBonusRate = 0.025;
					}
				}else{
					if (Math.abs(vnPc) >= 0 && Math.abs(vnPc) <= 9599999) {
						vnBonusRate = 0;
					} else if (Math.abs(vnPc) >= 9600000 && Math.abs(vnPc) <= 19199999) {
						vnBonusRate = 0.015;
					} else if (Math.abs(vnPc) >= 19200000 && Math.abs(vnPc) <= 28799999) {
						vnBonusRate = 0.025;
					} else if (Math.abs(vnPc) >= 28800000 && Math.abs(vnPc) <= 38399999) {
						vnBonusRate = 0.03;
					} else if (Math.abs(vnPc) >= 38400000 && Math.abs(vnPc) <= 47999999) {
						vnBonusRate = 0.035;
					} else {
						vnBonusRate = 0.04;
					}
				}
				
				String ckPersistencyCredit = "";
				if (paCalculationType.equals("quarter")) {
					if (voGACommissionPositiveDAO.getPersistencyCreditByQuarterly(
							vaAgentNo, paMonth3, paPersistencyCategory) == null) {
						ckPersistencyCredit = "T";
					} else {
						vnPersistencyRate = Double
								.parseDouble(voGACommissionPositiveDAO
										.getPersistencyCreditByQuarterly(vaAgentNo,
												paMonth3, paPersistencyCategory)
										.get("PRST_RTE").toString());
					}
				} else {
					if (voGACommissionPositiveDAO.getPersistencyCreditByAnnual(
							vaAgentNo, paYear, paPersistencyCategory) == null) {
						ckPersistencyCredit = "T";
					} else {
						vnPersistencyRate = Double
								.parseDouble(voGACommissionPositiveDAO
										.getPersistencyCreditByAnnual(vaAgentNo,
												paYear, paPersistencyCategory)
										.get("PRST_RTE").toString());
					}
				}

				if (vnPersistencyRate >= 0 && vnPersistencyRate <= 59.99) {
					vnPersistencyCredit = 0;
				} else if (vnPersistencyRate >= 60 && vnPersistencyRate <= 74.99) {
					vnPersistencyCredit = 0.5;
				} else if (vnPersistencyRate >= 75 && vnPersistencyRate <= 79.99) {
					vnPersistencyCredit = 0.9;
				} else if (vnPersistencyRate >= 80 && vnPersistencyRate <= 84.99) {
					vnPersistencyCredit = 1;
				} else if (vnPersistencyRate >= 85 && vnPersistencyRate <= 89.99) {
					vnPersistencyCredit = 1.1;
				} else {
					vnPersistencyCredit = 1.2;
				}
				if (paCalculationType.equals("quarter")) {
					if(voGACommissionPositiveDAO
							.getExperienceByQuarterly(vaAgentNo, paMonth3)!=null){
						vnExperience = Integer.parseInt(voGACommissionPositiveDAO
								.getExperienceByQuarterly(vaAgentNo, paMonth3)
								.get("FST_ENCO_MMS").toString());
					}
					
				} else {
					if(voGACommissionPositiveDAO
							.getExperienceByAnnual(vaAgentNo, paYear)!=null){
						vnExperience = Integer.parseInt(voGACommissionPositiveDAO
								.getExperienceByAnnual(vaAgentNo, paYear)
								.get("FST_ENCO_MMS").toString());
					}
					

				}
				
				if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
					vnPersistencyCredit = 1.00;
					

				}
				if (vnExperience > 0 && vnExperience <= 13
						&& vnPersistencyCredit >= 1) { // สำหรับตัวแทนใหม่ อายุงาน 1
														// - 13 เดือน
														// ทุกท่านที่มีอัตราความยั่งยืนมากกว่าหรือเท่ากับ
														// 80%

					vnPersistencyCredit = 1.00;

				}
				
				vnBonus = (vnPc * vnBonusRate * vnPersistencyCredit);
				
				String voCalculateQuarterly1[] = new String[3];
				String voCalculateQuarterly2[] = new String[3];
				String voCalculateQuarterly3[] = new String[3] ;
				String voCalculateQuarterly4[] = new String[3];
				
				if (paCalculationType.equals("annual")) {
					voCalculateQuarterly1 = calculateQuarterly(db, vaAgentNo,
							"1", paYear);
					voCalculateQuarterly2 = calculateQuarterly(db, vaAgentNo,
							"2", paYear);
					voCalculateQuarterly3 = calculateQuarterly(db, vaAgentNo,
							"3", paYear);
					voCalculateQuarterly4 = calculateQuarterly(db, vaAgentNo,
							"4", paYear);

					double vnBonus1 = Double.parseDouble(voCalculateQuarterly1[0]);
					double vnBonus2 = Double.parseDouble(voCalculateQuarterly2[0]);
					double vnBonus3 = Double.parseDouble(voCalculateQuarterly3[0]);
					double vnBonus4 = Double.parseDouble(voCalculateQuarterly4[0]);
					vnQuarterlySumBonus = vnBonus1 + vnBonus2 + vnBonus3 + vnBonus4;
					vnAnnualBonus = vnBonus;
					if(vnQuarterlySumBonus >=0){
						vnBonus = vnBonus - vnQuarterlySumBonus;
					}else{
						vnBonus = vnBonus + vnQuarterlySumBonus;
					}
					
				}
				if (vnBonus > 0) {
					if (paCalculationType.equals("annual")) {
						if (voCalculateQuarterly1[2].equals("false")
								|| voCalculateQuarterly2[2].equals("false")
								|| voCalculateQuarterly3[2].equals("false")
								|| voCalculateQuarterly4[2].equals("false")) {
							vnBonus = vnBonus * 0.8;
							if(vnQuarterlySumBonus >=0){
								vnBonus = (vnAnnualBonus*0.8)-vnQuarterlySumBonus;
							}else{
								vnBonus = (vnAnnualBonus*0.8)+vnQuarterlySumBonus;
							}
						}

					} else {
						
						if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
							vnBonus = vnBonus * 0.8;
						}
					}
				} 

				JSONArray voArray = new JSONArray();
				JSONArray voData = new JSONArray();
				voArray.add(i + 1);
				voArray.add(vaAgentNo);
				voArray.add(vaAgentName);
				voArray.add(df.format(vnPc));
				voArray.add(df.format(vnBonus));
				voData.add(vaAgentNo);
				voData.add(paCalculationType);
				voData.add(paQuarterly);
				voData.add(paYear);
				voArray.add(voData);

				voListData.add(voArray);

			}
		}
			voObject.put("aaData", voListData);
			voOut.print(voObject.toJSONString());

		}

	public String[] calculateQuarterly(Database db, String paAgentNo,
			String paQuarterly, String paYear) {

		String paMonth1 = "";
		String paMonth2 = "";
		String paMonth3 = "";
		String vaBonus100 = "true";
		String data[] = new String[3];
	
		if (paQuarterly.equals("1")) {
			paMonth1 = paYear + "01";
			paMonth2 = paYear + "02";
			paMonth3 = paYear + "03";
		} else if (paQuarterly.equals("2")) {
			paMonth1 = paYear + "04";
			paMonth2 = paYear + "05";
			paMonth3 = paYear + "06";
		} else if (paQuarterly.equals("3")) {
			paMonth1 = paYear + "07";
			paMonth2 = paYear + "08";
			paMonth3 = paYear + "09";
		} else if (paQuarterly.equals("4")) {
			paMonth1 = paYear + "10";
			paMonth2 = paYear + "10";
			paMonth3 = paYear + "11";
		}
		String paPersistencyCategory = "S11030B10507";
		// Database db = new Database();
		GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(
				db);
		GAAgentStructureDAO voGAAgentStructure = new GAAgentStructureDAO(db);
    	List<Map<String, Object>> vaVPInfo = voGAAgentStructure.findVPData(paAgentNo);

		double vnPc = 0;
		double vnBonusRate = 0;
		double vnBonus = 0;
		double vnBonus80 = 0;
		double vnBonus20 = 0;
		double vnPersistencyCredit = 0;
		double vnPersistencyRate = 0;
		int vnExperience = 0;
		String vaAgentNo = null;

		int size = 0;
		if (vaVPInfo != null) {
			size = vaVPInfo.size();
		}
		for (int i = 0; i < size; i++) {
			vnPc = 0;
			vnBonusRate = 0;
			vnBonus = 0;
			vnPersistencyCredit = 0;
			vnPersistencyRate = 0;
			vnExperience = 0;
			vaAgentNo = vaVPInfo.get(i).get("PE_NO").toString();


				vnPc = Double.parseDouble(voGACommissionPositiveDAO.getVPPCQuarter(
						vaAgentNo, paMonth1, paMonth2, paMonth3));


			if (Math.abs(vnPc) >= 0 && Math.abs(vnPc) <= 2399999) {
				vnBonusRate = 0;
			} else if (Math.abs(vnPc) >= 2400000 && Math.abs(vnPc) <= 4799999) {
				vnBonusRate = 0.005;
			} else if (Math.abs(vnPc) >= 4800000 && Math.abs(vnPc) <= 7199999) {
				vnBonusRate = 0.01;
			} else if (Math.abs(vnPc) >= 7200000 && Math.abs(vnPc) <= 9599999) {
				vnBonusRate = 0.015;
			} else if (Math.abs(vnPc) >= 9600000 && Math.abs(vnPc) <= 11999999) {
				vnBonusRate = 0.02;
			} else {
				vnBonusRate = 0.025;
			}
			String ckPersistencyCredit = "";
			if (voGACommissionPositiveDAO.getPersistencyCreditByQuarterly(
					vaAgentNo, paMonth3, paPersistencyCategory) == null) {
				ckPersistencyCredit = "T";
			} else {
				vnPersistencyRate = Double
						.parseDouble(voGACommissionPositiveDAO
								.getPersistencyCreditByQuarterly(vaAgentNo,
										paMonth3, paPersistencyCategory)
								.get("PRST_RTE").toString());
			}

			if (vnPersistencyRate >= 0 && vnPersistencyRate <= 59.99) {
				vnPersistencyCredit = 0;
			} else if (vnPersistencyRate >= 60 && vnPersistencyRate <= 74.99) {
				vnPersistencyCredit = 0.5;
			} else if (vnPersistencyRate >= 75 && vnPersistencyRate <= 79.99) {
				vnPersistencyCredit = 0.9;
			} else if (vnPersistencyRate >= 80 && vnPersistencyRate <= 84.99) {
				vnPersistencyCredit = 1;
			} else if (vnPersistencyRate >= 85 && vnPersistencyRate <= 89.99) {
				vnPersistencyCredit = 1.1;
			} else {
				vnPersistencyCredit = 1.2;
			}
			
			if(voGACommissionPositiveDAO
					.getExperienceByQuarterly(vaAgentNo, paMonth3)!=null){
				vnExperience = Integer.parseInt(voGACommissionPositiveDAO
						.getExperienceByQuarterly(vaAgentNo, paMonth3)
						.get("FST_ENCO_MMS").toString());
			}
			
			
			if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
				vnPersistencyCredit = 1.00;
				

			}
			if (vnExperience > 0 && vnExperience <= 13
					&& vnPersistencyCredit >= 1) { // สำหรับตัวแทนใหม่ อายุงาน 1
													// - 13 เดือน
													// ทุกท่านที่มีอัตราความยั่งยืนมากกว่าหรือเท่ากับ
													// 80%

				vnPersistencyCredit = 1.00;

			}
			
			vnBonus = (vnPc * vnBonusRate * vnPersistencyCredit);

			
			if (vnBonus > 0) {
				if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
					vnBonus80 = vnBonus * 0.8;
					vnBonus20 = vnBonus * 0.2;
					vnBonus = vnBonus80;
					vaBonus100 = "false";
				}
			} else {
				vnBonus = 0;
			}

		}
		data[0] = "" + vnBonus;
		data[1] = "" + vnBonus20;
		data[2] = "" + vaBonus100;

		// db.close();
		return data;
	}

		public void showData(HttpServletRequest request,
				HttpServletResponse response) throws ServletException, IOException {
			String paAgentNo = request.getParameter("agent_number");
			String paCalculationType = request.getParameter("calculation_type");
			String paQuarterly = request.getParameter("quarterly");
			String paYear = request.getParameter("year");
			String paMonth1 = "";
			String paMonth2 = "";
			String paMonth3 = "";
			if (paAgentNo.equals("")) {
				paAgentNo = "%";
			}
			if (paQuarterly.equals("1")) {
				paMonth1 = paYear + "01";
				paMonth2 = paYear + "02";
				paMonth3 = paYear + "03";
			} else if (paQuarterly.equals("2")) {
				paMonth1 = paYear + "04";
				paMonth2 = paYear + "05";
				paMonth3 = paYear + "06";
			} else if (paQuarterly.equals("3")) {
				paMonth1 = paYear + "07";
				paMonth2 = paYear + "08";
				paMonth3 = paYear + "09";
			} else if (paQuarterly.equals("4")) {
				paMonth1 = paYear + "10";
				paMonth2 = paYear + "10";
				paMonth3 = paYear + "11";
			}
			String paPersistencyCategory = "S11030B10107";
			Database db = new Database();
			GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(
					db);
			GAAgentStructureDAO voGAAgentStructure = new GAAgentStructureDAO(db);
	    	List<Map<String, Object>> vaVPInfo = voGAAgentStructure.findVPData(paAgentNo);

	    	double vnPc = 0;
			double vnBonusRate = 0;
			double vnBonus = 0;
			double vnBonus20 = 0;
			double vnAnnualBonus = 0;
			double vnHoldNetBonus = 0;
			double vnPersistencyCredit = 0;
			double vnPersistencyRate = 0;
			double vnQuarterlySumBonus = 0;
			int vnExperience = 0;
			String vaAgentNo = null;
			String vaAgentName = null;
			String vaBonusQ1 = "-";
			String vaBonusQ2 = "-";
			String vaBonusQ3 = "-";
			String vaBonusQ4 = "-";
			String vaSumBonusQ = "-";
			String vaBonusAnnual = "-";
			String vaBonusNetBonus = "-";
			String vaHoldQ1 = "-";
			String vaHoldQ2 = "-";
			String vaHoldQ3 = "-";
			String vaHoldQ4 = "-";
			String vaSumHoldQ = "-";
			String vaHoldAnnual = "-";
			String vaHoldNetBonus = "-";
			double vnBonusNetBonus = 0;
			double vnBonusAnnual = 0;
			double vnHoldAnnual = 0;
			DecimalFormat df = new DecimalFormat("#,##0.00");

			int size = 0;
			if (vaVPInfo != null) {
				size = vaVPInfo.size();
			}
				for (int i = 0; i < size; i++) {
				vnPc = 0;
				vnBonusRate = 0;
				vnBonus = 0;
				vnBonus20 = 0;
				vnAnnualBonus = 0;
				vnPersistencyCredit = 0;
				vnPersistencyRate = 0;
				vnQuarterlySumBonus = 0;
				vnExperience = 0;
				vaAgentNo = vaVPInfo.get(i).get("PE_NO").toString();

				if (paCalculationType.equals("quarter")) {
					vnPc = Double.parseDouble(voGACommissionPositiveDAO.getVPPCQuarter(
							vaAgentNo, paMonth1, paMonth2, paMonth3));

				} else {
					vnPc = Double.parseDouble(voGACommissionPositiveDAO.getVPPCAnnual(
							vaAgentNo, paYear));

				}
				vaAgentName = voGACommissionPositiveDAO.findNameById(vaAgentNo);

				if (paCalculationType.equals("quarter")) {
					
					if (Math.abs(vnPc) >= 0 && Math.abs(vnPc) <= 2399999) {
						vnBonusRate = 0;
					} else if (Math.abs(vnPc) >= 2400000 && Math.abs(vnPc) <= 4799999) {
						vnBonusRate = 0.005;
					} else if (Math.abs(vnPc) >= 4800000 && Math.abs(vnPc) <= 7199999) {
						vnBonusRate = 0.01;
					} else if (Math.abs(vnPc) >= 7200000 && Math.abs(vnPc) <= 9599999) {
						vnBonusRate = 0.015;
					} else if (Math.abs(vnPc) >= 9600000 && Math.abs(vnPc) <= 11999999) {
						vnBonusRate = 0.02;
					} else {
						vnBonusRate = 0.025;
					}
				}else{
					if (Math.abs(vnPc) >= 0 && Math.abs(vnPc) <= 9599999) {
						vnBonusRate = 0;
					} else if (Math.abs(vnPc) >= 9600000 && Math.abs(vnPc) <= 19199999) {
						vnBonusRate = 0.015;
					} else if (Math.abs(vnPc) >= 19200000 && Math.abs(vnPc) <= 28799999) {
						vnBonusRate = 0.025;
					} else if (Math.abs(vnPc) >= 28800000 && Math.abs(vnPc) <= 38399999) {
						vnBonusRate = 0.03;
					} else if (Math.abs(vnPc) >= 38400000 && Math.abs(vnPc) <= 47999999) {
						vnBonusRate = 0.035;
					} else {
						vnBonusRate = 0.04;
					}
				}
				String ckPersistencyCredit = "";
				if (paCalculationType.equals("quarter")) {
					if (voGACommissionPositiveDAO.getPersistencyCreditByQuarterly(
							vaAgentNo, paMonth3, paPersistencyCategory) == null) {
						ckPersistencyCredit = "T";
					} else {
						vnPersistencyRate = Double
								.parseDouble(voGACommissionPositiveDAO
										.getPersistencyCreditByQuarterly(vaAgentNo,
												paMonth3, paPersistencyCategory)
										.get("PRST_RTE").toString());
					}
				} else {
					if (voGACommissionPositiveDAO.getPersistencyCreditByAnnual(
							vaAgentNo, paYear, paPersistencyCategory) == null) {
						ckPersistencyCredit = "T";
					} else {
						vnPersistencyRate = Double
								.parseDouble(voGACommissionPositiveDAO
										.getPersistencyCreditByAnnual(vaAgentNo,
												paYear, paPersistencyCategory)
										.get("PRST_RTE").toString());
					}
				}
				if (vnPersistencyRate >= 1 && vnPersistencyRate <= 59.99) {
					vnPersistencyCredit = 0;
				} else if (vnPersistencyRate >= 60 && vnPersistencyRate <= 74.99) {
					vnPersistencyCredit = 0.5;
				} else if (vnPersistencyRate >= 75 && vnPersistencyRate <= 79.99) {
					vnPersistencyCredit = 0.9;
				} else if (vnPersistencyRate >= 80 && vnPersistencyRate <= 84.99) {
					vnPersistencyCredit = 1;
				} else if (vnPersistencyRate >= 85 && vnPersistencyRate <= 89.99) {
					vnPersistencyCredit = 1.1;
				} else  {
					vnPersistencyCredit = 1.2;
				}
				if (paCalculationType.equals("quarter")) {
					if(voGACommissionPositiveDAO
							.getExperienceByQuarterly(vaAgentNo, paMonth3)!=null){
						vnExperience = Integer.parseInt(voGACommissionPositiveDAO
								.getExperienceByQuarterly(vaAgentNo, paMonth3)
								.get("FST_ENCO_MMS").toString());
					}
					
				} else {
					if(voGACommissionPositiveDAO
							.getExperienceByAnnual(vaAgentNo, paYear)!=null){
						vnExperience = Integer.parseInt(voGACommissionPositiveDAO
								.getExperienceByAnnual(vaAgentNo, paYear)
								.get("FST_ENCO_MMS").toString());

					}
					
				}
				
				if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
					vnPersistencyCredit = 1.00;

				}
				if (vnExperience > 0 && vnExperience <= 13
						&& vnPersistencyCredit >= 1) { // สำหรับตัวแทนใหม่ อายุงาน 1
														// - 13 เดือน
														// ทุกท่านที่มีอัตราความยั่งยืนมากกว่าหรือเท่ากับ
														// 80%

					vnPersistencyCredit = 1.00;

				}
				
				vnBonus = (vnPc * vnBonusRate * vnPersistencyCredit);
			
				String voCalculateQuarterly1[] = new String[3];
				String voCalculateQuarterly2[] = new String[3];
				String voCalculateQuarterly3[] = new String[3] ;
				String voCalculateQuarterly4[] = new String[3];
				
				if (paCalculationType.equals("annual")) {
					
					voCalculateQuarterly1 = calculateQuarterly(db, vaAgentNo,
							"1", paYear);
					voCalculateQuarterly2 = calculateQuarterly(db, vaAgentNo,
							"2", paYear);
					voCalculateQuarterly3 = calculateQuarterly(db, vaAgentNo,
							"3", paYear);
					voCalculateQuarterly4 = calculateQuarterly(db, vaAgentNo,
							"4", paYear);

					double vnBonus1 = Double.parseDouble(voCalculateQuarterly1[0]);
					double vnBonus2 = Double.parseDouble(voCalculateQuarterly2[0]);
					double vnBonus3 = Double.parseDouble(voCalculateQuarterly3[0]);
					double vnBonus4 = Double.parseDouble(voCalculateQuarterly4[0]);
					double vnHold1 = Double.parseDouble(voCalculateQuarterly1[1]);
					double vnHold2 = Double.parseDouble(voCalculateQuarterly2[1]);
					double vnHold3 = Double.parseDouble(voCalculateQuarterly3[1]);
					double vnHold4 = Double.parseDouble(voCalculateQuarterly4[1]);
					
					vnAnnualBonus = vnBonus;
					vnBonusAnnual = vnAnnualBonus;
					vnQuarterlySumBonus = vnBonus1 + vnBonus2 + vnBonus3 + vnBonus4;
					if(vnQuarterlySumBonus >=0){
						vnBonus = vnBonus - vnQuarterlySumBonus;
					}else{
						vnBonus = vnBonus + vnQuarterlySumBonus;
					}
					vaBonusQ1 = "" + df.format(vnBonus1);
					vaBonusQ2 = "" + df.format(vnBonus2);
					vaBonusQ3 = "" + df.format(vnBonus3);
					vaBonusQ4 = "" + df.format(vnBonus4);
					vaSumBonusQ = "" + df.format(vnQuarterlySumBonus);
					vaHoldQ1 = "" + df.format(vnHold1);
					vaHoldQ2 = "" + df.format(vnHold2);
					vaHoldQ3 = "" + df.format(vnHold3);
					vaHoldQ4 = "" + df.format(vnHold4);
					vnHoldNetBonus = vnHold1 + vnHold2 + vnHold3 + vnHold4;
					vaSumHoldQ = "" + df.format(vnHoldNetBonus);

				}
				if (vnBonus > 0) {
					if (paCalculationType.equals("annual")) {
						if (voCalculateQuarterly1[2].equals("false")
								|| voCalculateQuarterly2[2].equals("false")
								|| voCalculateQuarterly3[2].equals("false")
								|| voCalculateQuarterly4[2].equals("false")) {
							vnBonus = vnBonus * 0.8;
							vnBonus20 = (vnBonus / 0.8) * 0.2;
							if(vnQuarterlySumBonus >=0){
								vnBonus = (vnAnnualBonus*0.8)-vnQuarterlySumBonus;
							}else{
								vnBonus = (vnAnnualBonus*0.8)+vnQuarterlySumBonus;
							}
							
							vnBonusAnnual = vnAnnualBonus*0.8;
							vnHoldAnnual = vnAnnualBonus*0.2;
						}

					} else {
						
						if (ckPersistencyCredit.equals("T")) { // สำหรับตัวแทนทุกท่านที่ยังไม่มีอัตราความยั่งยืน
							vnBonus = vnBonus * 0.8;
							vnBonus20 = (vnBonus / 0.8) * 0.2;
							
						}
					}

				}
				
			}

			if (paCalculationType.equals("annual")) {
				paCalculationType = "Annual";
				vaBonusNetBonus = "" + df.format(vnBonus);
				vaBonusAnnual = "" + df.format(vnBonusAnnual);
				vaHoldAnnual = "" + df.format(vnHoldAnnual);
				vaHoldNetBonus = "" + df.format(vnHoldAnnual);
			} else {
				paCalculationType = "Quarterly";
				if (paQuarterly.equals("1")) {
					vaBonusQ1 = "" + df.format(vnBonus);
					vaHoldQ1 = "" + df.format(vnBonus20);
				}
				if (paQuarterly.equals("2")) {
					vaBonusQ2 = "" + df.format(vnBonus);
					vaHoldQ2 = "" + df.format(vnBonus20);
				}
				if (paQuarterly.equals("3")) {
					vaBonusQ3 = "" + df.format(vnBonus);
					vaHoldQ3 = "" + df.format(vnBonus20);
				}
				if (paQuarterly.equals("4")) {
					vaBonusQ4 = "" + df.format(vnBonus);
					vaHoldQ4 = "" + df.format(vnBonus20);
				}
			}
			String paRate = "";
			if (vnPersistencyRate == 0) {
				paRate = "N/A";
			} else {
				paRate = "" + df.format(vnPersistencyRate);
			}
			request.setAttribute("calculationType", paCalculationType);
			request.setAttribute("agentNo", vaAgentNo);
			request.setAttribute("agentName", vaAgentName);
			request.setAttribute("bonus", df.format(vnBonus));
			request.setAttribute("quarterly", paQuarterly);
			request.setAttribute("year", paYear);
			request.setAttribute("pc", df.format(vnPc));
			request.setAttribute("bonusRate", df.format(vnBonusRate * 100));
			request.setAttribute("persistencyRate", paRate);
			request.setAttribute("persistencyCredit",
					df.format(vnPersistencyCredit * 100));
			request.setAttribute("bonusQ1", vaBonusQ1);
			request.setAttribute("bonusQ2", vaBonusQ2);
			request.setAttribute("bonusQ3", vaBonusQ3);
			request.setAttribute("bonusQ4", vaBonusQ4);
			request.setAttribute("sumBonusQ", vaSumBonusQ);
			request.setAttribute("bonusAnnual", vaBonusAnnual);
			request.setAttribute("bonusNetBonus", vaBonusNetBonus);
			request.setAttribute("holdQ1", vaHoldQ1);
			request.setAttribute("holdQ2", vaHoldQ2);
			request.setAttribute("holdQ3", vaHoldQ3);
			request.setAttribute("holdQ4", vaHoldQ4);
			request.setAttribute("sumHoldQ", vaSumHoldQ);
			request.setAttribute("holdAnnual", vaHoldAnnual);
			request.setAttribute("holdNetBonus", vaHoldNetBonus);

			request.setAttribute("quarterlySumBonus",
					df.format(vnQuarterlySumBonus));
			request.setAttribute("annualBonus", df.format(vnAnnualBonus));

			RequestDispatcher rd = request.getRequestDispatcher("content/ACM1104_1.jsp");
			rd.forward(request, response);
		}

    private void getQuarter (HttpServletRequest request, HttpServletResponse response) throws IOException {
    	String vaPeNo = request.getParameter("peNo");
		String vaQuarter = request.getParameter("paQuarter");
		String vaYear = request.getParameter("paYear");
    	
    	Database db = new Database();
    	GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(db);
		
		JSONObject voObject = new JSONObject();
		JSONArray voArrayGM = new JSONArray(); 
		JSONArray voArrayTier1 = new JSONArray(); 
		JSONArray voArrayTier2 = new JSONArray(); 
		PrintWriter out = response.getWriter();
		
		double pnResltGMTotal = 0;
		double pnResltTier1Total = 0;
		double pnResltTier2Total = 0;
		
		DecimalFormat voDecimalFormat = new DecimalFormat("#,##0.00");
		String pnResult = "empty";
		
		try {
			List<Map<String, Object>> voGMResultQuarter = voGACommissionPositiveDAO.getGMResultQuarter(vaPeNo, vaQuarter, vaYear);
			List<Map<String, Object>> voGMResultTier1Quarter = voGACommissionPositiveDAO.getGMResultTier1Quarter(vaPeNo, vaQuarter, vaYear);
			List<Map<String, Object>> voGMResultTier2Quarter = voGACommissionPositiveDAO.getGMResultTier2Quarter(vaPeNo, vaQuarter, vaYear);
			
			if(voGMResultQuarter!=null){
				for (int i = 0; i < voGMResultQuarter.size(); i++) {

					Map<String, Object> voObjectGM = voGMResultQuarter.get(i);
					
					voArrayGM.add(voObjectGM.get("PE_NO").toString()+"|"+
							voObjectGM.get("NM_TITLE_THAI").toString()+" "+voObjectGM.get("NM_THAI").toString()+" "+voObjectGM.get("SURNM_THAI").toString()+"|"+
							voObjectGM.get("DTY_CD").toString()+"|"+voDecimalFormat.format(Double.parseDouble(voObjectGM.get("PC").toString())));

					pnResltGMTotal += Double.parseDouble(voObjectGM.get("PC").toString());
					pnResult = "notEmpty";
				}
				
			}else{
				voArrayGM.add(vaPeNo+"|"+
						voGACommissionPositiveDAO.findNameById(vaPeNo)+"|S10240201|0.00");
				
			}
			
			if(voGMResultTier1Quarter!=null){
				for (int i = 0; i < voGMResultTier1Quarter.size(); i++) {

					Map<String, Object> voObjectTier1 = voGMResultTier1Quarter.get(i);
					
					String vnTmpPC = "";
					if(voObjectTier1.get("PC") == null){
						vnTmpPC = "0.00";
					}else {
						vnTmpPC = voObjectTier1.get("PC").toString();
					}
					
					voArrayTier1.add(voObjectTier1.get("PE_NO")+"|"+
							voGACommissionPositiveDAO.findNameById(voObjectTier1.get("PE_NO").toString())+"|"+
							voGACommissionPositiveDAO.findDtyCDById(voObjectTier1.get("PE_NO").toString())+"|"+voDecimalFormat.format(Double.parseDouble(vnTmpPC)));

					
					pnResltTier1Total += Double.parseDouble(vnTmpPC);
					pnResult = "notEmpty";
				}
				
			}
			
			if(voGMResultTier2Quarter!=null){
				for (int i = 0; i < voGMResultTier2Quarter.size(); i++) {
	
					Map<String, Object> voObjectTier2 = voGMResultTier2Quarter.get(i);
					
					voArrayTier2.add(voObjectTier2.get("PE_NO").toString()+"|"+
							voObjectTier2.get("NM_TITLE_THAI").toString()+" "+voObjectTier2.get("NM_THAI").toString()+" "+voObjectTier2.get("SURNM_THAI").toString()+"|"+
							voObjectTier2.get("DTY_CD").toString()+"|"+voDecimalFormat.format(Double.parseDouble(voObjectTier2.get("PC").toString())));
	
					pnResltTier2Total += Double.parseDouble(voObjectTier2.get("PC").toString());
					pnResult = "notEmpty";
				}
				
			}
			
			voObject.put("result", pnResult);
			
			voObject.put("resultGM", voArrayGM);
			voObject.put("resultTier1", voArrayTier1);
			voObject.put("resultTier2", voArrayTier2);
			
			voObject.put("pcGM", voDecimalFormat.format(pnResltGMTotal));
			voObject.put("pcTier1", voDecimalFormat.format(pnResltTier1Total));
			voObject.put("pcTier2", voDecimalFormat.format(pnResltTier2Total));
			
			out.print(voObject.toJSONString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			db.close();
		}
		
    }
    
    private void getAnnual(HttpServletRequest request, HttpServletResponse response) throws IOException {
    	String vaPeNo = request.getParameter("peNo");
		String vaYear = request.getParameter("paYear");
    	
    	Database db = new Database();
    	GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(db);
		
		JSONObject voObject = new JSONObject();
		JSONArray voArrayGM = new JSONArray(); 
		JSONArray voArrayTier1 = new JSONArray(); 
		JSONArray voArrayTier2 = new JSONArray(); 
		PrintWriter out = response.getWriter();
		
		double pnResltGMTotal = 0;
		double pnResltTier1Total = 0;
		double pnResltTier2Total = 0;
		String pnResult = "empty";
		
		DecimalFormat voDecimalFormat = new DecimalFormat("#,##0.00");
		
		try {
			List<Map<String, Object>> voGMResultAnnual = voGACommissionPositiveDAO.getGMResultAnnual(vaPeNo, vaYear);
			List<Map<String, Object>> voGMResultTier1Annual = voGACommissionPositiveDAO.getGMResultTier1Annual(vaPeNo, vaYear);
			List<Map<String, Object>> voGMResultTier2Annual = voGACommissionPositiveDAO.getGMResultTier2Annual(vaPeNo, vaYear);
			
			if(voGMResultAnnual!=null){
				for (int i = 0; i < voGMResultAnnual.size(); i++) {

					Map<String, Object> voObjectGM = voGMResultAnnual.get(i);
					
					voArrayGM.add(voObjectGM.get("PE_NO").toString()+"|"+
							voObjectGM.get("NM_TITLE_THAI").toString()+" "+voObjectGM.get("NM_THAI").toString()+" "+voObjectGM.get("SURNM_THAI").toString()+"|"+
							voObjectGM.get("DTY_CD").toString()+"|"+voDecimalFormat.format(Double.parseDouble(voObjectGM.get("PC").toString())));

					pnResltGMTotal += Double.parseDouble(voObjectGM.get("PC").toString());
				}
				
				pnResult = "notEmpty";
			}
			
	
			if(voGMResultTier1Annual!=null){
				for (int i = 0; i < voGMResultTier1Annual.size(); i++) {

					Map<String, Object> voObjectTier1 = voGMResultTier1Annual.get(i);
					
					voArrayTier1.add(voObjectTier1.get("PE_NO").toString()+"|"+
							voGACommissionPositiveDAO.findNameById(voObjectTier1.get("PE_NO").toString())+"|"+
							voGACommissionPositiveDAO.findDtyCDById(voObjectTier1.get("PE_NO").toString())+"|"+voDecimalFormat.format(Double.parseDouble(voObjectTier1.get("PC").toString())));

					pnResltTier1Total += Double.parseDouble(voObjectTier1.get("PC").toString());
				}
			}
			
			
			if(voGMResultTier2Annual!=null){
				for (int i = 0; i < voGMResultTier2Annual.size(); i++) {

					Map<String, Object> voObjectTier2 = voGMResultTier2Annual.get(i);
					
					voArrayTier2.add(voObjectTier2.get("PE_NO").toString()+"|"+
							voObjectTier2.get("NM_TITLE_THAI").toString()+" "+voObjectTier2.get("NM_THAI").toString()+" "+voObjectTier2.get("SURNM_THAI").toString()+"|"+
							voObjectTier2.get("DTY_CD").toString()+"|"+voDecimalFormat.format(Double.parseDouble(voObjectTier2.get("PC").toString())));

					pnResltTier2Total += Double.parseDouble(voObjectTier2.get("PC").toString());
				}
			}
			
			voObject.put("result", pnResult);
			voObject.put("resultGM", voArrayGM);
			voObject.put("resultTier1", voArrayTier1);
			voObject.put("resultTier2", voArrayTier2);
			
			voObject.put("pcGM", voDecimalFormat.format(pnResltGMTotal));
			voObject.put("pcTier1", voDecimalFormat.format(pnResltTier1Total));
			voObject.put("pcTier2", voDecimalFormat.format(pnResltTier2Total));
			
			out.print(voObject.toJSONString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			db.close();
		}
		
    }
    
    @SuppressWarnings("unchecked")
	public void getDataDetailTable(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		int vnStart = Integer.parseInt(request.getParameter("iDisplayStart")); // 0
		int vnAmount = Integer.parseInt(request.getParameter("iDisplayLength")); // 10
		String vaDraw = request.getParameter("sEcho");
		String vaPeNo = request.getParameter("sSearch_0").trim();
		String paCalculationType = request.getParameter("sSearch_1").trim();
		String paQuarterly = request.getParameter("sSearch_2").trim();
		String paYear = request.getParameter("sSearch_3").trim();
		String paMonth1 = "";
		String paMonth2 = "";
		String paMonth3 = "";

		if (vaPeNo.equals("")) {
			vaPeNo = "%";
		}
		if (paQuarterly.equals("1")) {
			paMonth1 = paYear + "01";
			paMonth2 = paYear + "02";
			paMonth3 = paYear + "03";
		} else if (paQuarterly.equals("2")) {
			paMonth1 = paYear + "04";
			paMonth2 = paYear + "05";
			paMonth3 = paYear + "06";
		} else if (paQuarterly.equals("3")) {
			paMonth1 = paYear + "07";
			paMonth2 = paYear + "08";
			paMonth3 = paYear + "09";
		} else if (paQuarterly.equals("4")) {
			paMonth1 = paYear + "10";
			paMonth2 = paYear + "10";
			paMonth3 = paYear + "11";
		}
		// get data from database
		int vnTotal = 0;
		int vnTotalAfterFilter = 0;
		List<Map<String, String>> voResult = new ArrayList<Map<String, String>>();

		Database db = new Database();
		GACommissionPositiveDAO voGACommissionPositiveDAO = new GACommissionPositiveDAO(
				db);
		List<Map<String, Object>> vaVPInfo = null;

		if (paCalculationType.equals("Quarterly")) {
			vaVPInfo = voGACommissionPositiveDAO.getVPPCQuarterDetail(
					vaPeNo, paMonth1, paMonth2, paMonth3);

		} else {
			vaVPInfo = voGACommissionPositiveDAO.getVPPCAnnualDetail(
					vaPeNo, paYear);

		}

		try {
			if(vaVPInfo!=null){
				vnTotal = vaVPInfo.size();
				if (vnAmount == -1) {
					for (int i = 0; i < vaVPInfo.size(); i++) {

						Map<String, String> voTmp = new HashMap<String, String>();
						voTmp.put("CLOS_YM", vaVPInfo.get(i).get("CLOS_YM")
								.toString());
						voTmp.put("PE_NO_COL", vaVPInfo.get(i).get("PE_NO_COL")
								.toString());
						voTmp.put("PO_NO", vaVPInfo.get(i).get("PO_NO").toString());
						voTmp.put("AFYP", vaVPInfo.get(i).get("AFYP").toString());
						voTmp.put("ARYP2", vaVPInfo.get(i).get("ARYP2").toString());
						voTmp.put("PC", vaVPInfo.get(i).get("PC").toString());
						voResult.add(voTmp);
					}
					vnTotalAfterFilter = vnTotal;
				} else {
					if (vaVPInfo.size() != 0) {
						for (int i = vnStart; i < (vnStart + vnAmount); i++) {

							Map<String, String> voTmp = new HashMap<String, String>();
							voTmp.put("CLOS_YM", vaVPInfo.get(i).get("CLOS_YM")
									.toString());
							voTmp.put("PE_NO_COL", vaVPInfo.get(i).get("PE_NO_COL")
									.toString());
							voTmp.put("PO_NO", vaVPInfo.get(i).get("PO_NO")
									.toString());
							voTmp.put("AFYP", vaVPInfo.get(i).get("AFYP")
									.toString());
							voTmp.put("ARYP2", vaVPInfo.get(i).get("ARYP2")
									.toString());
							voTmp.put(
									"PC",
									""
											+ (Double.parseDouble(vaVPInfo.get(i)
													.get("AFYP").toString()) + Double
													.parseDouble(vaVPInfo.get(i)
															.get("ARYP2")
															.toString())));
							voResult.add(voTmp);
							if (i == (vaVPInfo.size() - 1))
								break;
						}
					}
					vnTotalAfterFilter = vnTotal;
				}
			}
			
		} finally {
			db.close();
		}

		DecimalFormat voDecimalFormat = new DecimalFormat("#,##0.00");
		// prepare data to show in dataTable
		PrintWriter voOut = response.getWriter();
		// set sequence
		int[] vnSequence = new int[voResult.size()];
		for (int i = 1; i <= voResult.size(); i++)
			vnSequence[i - 1] = i + vnStart;
		try {
			JSONObject voObject = new JSONObject();
			JSONArray voListData = new JSONArray();
			for (int i = 0; i < voResult.size(); i++) {
				Map<String, String> voTmp = voResult.get(i);
				JSONArray voArray = new JSONArray();
				voArray.add(vnSequence[i]);
				voArray.add(voTmp.get("CLOS_YM"));
				voArray.add(voTmp.get("PE_NO_COL"));
				voArray.add(voTmp.get("PO_NO"));
				voArray.add(voDecimalFormat.format(Double.parseDouble(voTmp
						.get("AFYP"))));
				voArray.add(voDecimalFormat.format(Double.parseDouble(voTmp
						.get("ARYP2"))));
				voArray.add(voDecimalFormat.format(Double.parseDouble(voTmp
						.get("PC"))));
				// adding array data into list
				voListData.add(voArray);
			}
			voObject.put("sEcho", Integer.parseInt(vaDraw));
			voObject.put("iTotalRecords", vnTotal);
			voObject.put("iTotalDisplayRecords", vnTotalAfterFilter);
			voObject.put("aaData", voListData);
			voOut.print(voObject.toJSONString());
		} finally {
			voOut.close();
		}
	}
    
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		processRequest(request, response);
	}


	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		processRequest(request, response);
	}

}
